import pandas as pd
import numpy as np

# 导入数据
data = pd.read_csv(r"C:\Users\Lenovo\Desktop\Insightful & Vast USA Statistics\每个UID的负债指数.csv")
state_name = data['state'].unique()

Debt_measure = []

for i in state_name:
    Data_i = pd.read_excel(r"C:\Users\Lenovo\Desktop\Insightful & Vast USA Statistics\根据state拆分的表格\拆分表格.xlsx", sheet_name = i, usecols = ["Debt_index", "Population_Density"])
    measure = Data_i["Debt_index"].sum()/Data_i["Population_Density"].sum()
    Debt_measure.append(measure)

Debt_measure = pd.DataFrame(Debt_measure)
state_name = pd.DataFrame(state_name)

# 连接表格
Debt_measure = pd.concat([state_name, Debt_measure], axis=1)
# print(Debt_measure)
Debt_measure.to_csv(r"C:\Users\Lenovo\Desktop\Insightful & Vast USA Statistics\每个state的负债测度.csv")